﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using UrlSummary.Models;

namespace UrlSummary.Controllers
{
    public class UrlController : Controller
    {
        // GET: Url

        #region  URL表操作
        public ActionResult Index()
        {
            ViewBag.UrlTypeList = GetUrlTypeList();
            return View();
        }

        /// <summary>
        /// Gets the URL list.  获取URL表详情
        /// </summary>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/8 10:10
        public ActionResult GetUrlList()
        {
            try
            {
                int seach_TypeId = Convert.ToInt32(Request["seach_TypeId"]);
                string seach_UrlLike = Request["seach_UrlLike"];
                //当前页数
                int pageIndex = Convert.ToInt32(Request.Params["page"]);
                //每页条数的选择项
                int pageSize = Convert.ToInt32(Request.Params["limit"]);

                pageIndex = (pageIndex - 1) * pageSize;

                IList<UrlAndTypeModel> list = GetUrlAndTypes(seach_TypeId, seach_UrlLike, pageIndex, pageSize);
                int count = GetCount(seach_TypeId, seach_UrlLike);
                var json = new { count, code = 0, msg = "", data = list, };
                return Json(json);
            }
            catch (Exception x)
            {
                var json = new { code = 2, msg = x.Message, count = 0, data = "", };
                return Json(json);
            }
        }

        /// <summary>
        /// Adds the and update. 删除修改时调入的方法
        /// </summary>
        /// <param name="urlModel">The URL model.</param>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/8 10:05
        public ActionResult AddAndUpdate(UrlModel urlModel)
        {
            ResultModel result = new ResultModel();
            //判断是否编辑重复的数据
            string strSql1 = "SELECT COUNT(1) FROM dbo.T_Url WHERE UrlName='" + urlModel.UrlName + "' AND  UrlAddress='" + urlModel.UrlAddress + "'  AND UrlType=" + urlModel.UrlType + "";
            if (Convert.ToInt32(DBHelper.ExecuteScalar(strSql1)) > 0)
            {
                result.Code = 2;
            }
            else
            {
                //如果等于0时  是创建  不是0时修改
                if (urlModel.UrlId == 0)
                {
                    string strSql = "INSERT  INTO dbo.T_Url( UrlName ,  UrlAddress ,  UrlContent ,  UrlType ,  UrlCreateTime) VALUES  ( '" + urlModel.UrlName + "' ,   '" + urlModel.UrlAddress + "' ,    '" + urlModel.UrlContent + "' ,  " + urlModel.UrlType + " ,    GETDATE()  )";
                    try
                    {
                        if (DBHelper.ExecuteNonQuery(strSql) > 0)
                        {
                            result.Code = 1;
                        }
                    }
                    catch (Exception x)
                    {
                        result.Code = 0;
                        result.Msg = x.Message;
                    }
                }
                else
                {
                    string strSql = "UPDATE dbo.T_Url SET UrlName='" + urlModel.UrlName + "',UrlAddress='" + urlModel.UrlAddress + "',UrlContent='" + urlModel.UrlContent + "',UrlType='" + urlModel.UrlType + "' WHERE UrlId=" + urlModel.UrlId;
                    try
                    {
                        if (DBHelper.ExecuteNonQuery(strSql) > 0)
                        {
                            result.Code = 1;
                        }
                    }
                    catch (Exception x)
                    {
                        result.Code = 0;
                        result.Msg = x.Message;
                    }
                }
            }
            return Json(result);
        }


        /// <summary>
        /// Gets the count.
        /// </summary>
        /// <param name="seach_TypeId">The seach type identifier.</param>
        /// <param name="seach_UrlLike">The seach URL like.</param>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/23 13:51
        public int GetCount(int seach_TypeId, string seach_UrlLike)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT COUNT(1) FROM dbo.T_Url a INNER JOIN dbo.T_UrlType b ON a.UrlType=b.TypeId  WHERE 1=1 ");
            if (seach_TypeId > 0)
            {
                stringBuilder.Append(" AND a.UrlType=" + seach_TypeId + " ");
            }

            if (!string.IsNullOrWhiteSpace(seach_UrlLike))
            {
                stringBuilder.Append(" AND (a.UrlName LIKE '%" + seach_UrlLike + "%' OR a.UrlAddress  LIKE '%" + seach_UrlLike + "%'  OR a.UrlContent  LIKE '%" + seach_UrlLike + "%') ");
            }
            return Convert.ToInt32(DBHelper.ExecuteScalar(stringBuilder.ToString()));
        }

        /// <summary>
        /// Gets the URL and types.  获取url数据表的方法
        /// </summary>
        /// <param name="seach_TypeId">The seach type identifier.</param>
        /// <param name="seach_UrlLike">The seach URL like.</param>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/10 10:15
        public IList<UrlAndTypeModel> GetUrlAndTypes(int seach_TypeId, string seach_UrlLike, int pageIndex, int pageSize)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT a.UrlId,a.UrlName,a.UrlAddress,a.UrlContent,a.UrlType,a.UrlCreateTime,b.TypeId,b.TypeName,b.TypeContent,b.TypeCreateTime, ROW_NUMBER() OVER (ORDER BY a.UrlId DESC) RowNum FROM dbo.T_Url a INNER JOIN dbo.T_UrlType b ON a.UrlType=b.TypeId WHERE 1=1 ");
            if (seach_TypeId > 0)
            {
                stringBuilder.Append(" AND a.UrlType=" + seach_TypeId + " ");
            }
            if (!string.IsNullOrWhiteSpace(seach_UrlLike))
            {
                stringBuilder.Append(" AND (a.UrlName LIKE '%" + seach_UrlLike + "%' OR a.UrlAddress  LIKE '%" + seach_UrlLike + "%'  OR a.UrlContent  LIKE '%" + seach_UrlLike + "%') ");
            }
            stringBuilder.Append(" ORDER BY a.UrlId DESC ");
            stringBuilder.Append(" OFFSET " + pageIndex + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY ");
            IList<UrlAndTypeModel> urlList = DBHelper.ExecuteObjects<UrlAndTypeModel>(stringBuilder.ToString());
            return urlList;
        }

        /// <summary>
        /// Deletes the URL and type by identifier.  删除url表的数据
        /// </summary>
        /// <param name="UrlId">The URL identifier.</param>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/9 10:10
        public ActionResult DelUrlAndTypeById(int UrlId)
        {
            string strSql = "DELETE FROM dbo.T_Url WHERE UrlId=" + UrlId;
            ResultModel result = new ResultModel();
            try
            {
                if (DBHelper.ExecuteNonQuery(strSql) > 0)
                {
                    result.Code = 1;
                }
            }
            catch (Exception x)
            {
                result.Code = 0;
                result.Msg = x.Message;
            }
            return Json(result);
        }
        #endregion

        #region URL类型操作

        /// <summary>
        /// Gets the URL type list.获取URL类型
        /// </summary>
        /// <returns></returns>
        /// 创建人：刘靖文
        /// 创建时间：2019/5/8 10:47
        public IList<UrlTypeModel> GetUrlTypeList()
        {
            string strSql = "SELECT ROW_NUMBER() OVER (ORDER BY TypeId ASC) RowNum, TypeId,TypeName,TypeContent,TypeCreateTime FROM dbo.T_UrlType";
            IList<UrlTypeModel> urlTypeList = DBHelper.ExecuteObjects<UrlTypeModel>(strSql);
            return urlTypeList;
        }

        /// <summary>
        /// Reports the URL type list.  初始化页面
        /// </summary>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/10 13:32
        public ActionResult ReportUrlTypeList()
        {
            return View();
        }
        /// <summary>
        /// Gets the report URL type list.获取数据
        /// </summary>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/10 15:33
        public ActionResult GetReportUrlTypeList()
        {
            try
            {
                IList<UrlTypeModel> list = GetUrlTypeList();
                //int count = QCBLL.SupplierBLL.GetCount(ht);
                var json = new { count = 0, code = 0, msg = "", data = list, };
                return Json(json);
            }
            catch (Exception x)
            {
                var json = new { code = 2, msg = x.Message, count = 0, data = "", };
                return Json(json);
            }
        }

        /// <summary>
        /// Adds the type of the and update URL.  创建和修改时调入的方法
        /// </summary>
        /// <param name="urlTypeModel">The URL type model.</param>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/10 15:11
        public ActionResult AddAndUpdateUrlType(UrlTypeModel urlTypeModel)
        {
            ResultModel result = new ResultModel();
            //提示已存在
            string strSql1 = "SELECT COUNT(1) FROM dbo.T_UrlType WHERE TypeName='" + urlTypeModel.TypeName + "'";
            if (Convert.ToInt32(DBHelper.ExecuteScalar(strSql1)) > 0)
            {
                result.Code = 2;
            }
            else
            {
                //如果等于0时  是创建  不是0时修改
                if (urlTypeModel.TypeId == 0)
                {
                    try
                    {
                        string strSqli = "INSERT INTO dbo.T_UrlType ( TypeName , TypeContent , TypeCreateTime )VALUES  ( '" + urlTypeModel.TypeName + "' ,   '" + urlTypeModel.TypeContent + "' ,   GETDATE() )";
                        if (DBHelper.ExecuteNonQuery(strSqli) > 0)
                        {
                            result.Code = 1;
                        }
                    }
                    catch (Exception x)
                    {
                        result.Code = 0;
                        result.Msg = x.Message;
                    }
                }
                else
                {
                    string strSql = "UPDATE dbo.T_UrlType  SET TypeName='" + urlTypeModel.TypeName + "',TypeContent='" + urlTypeModel.TypeContent + "' WHERE TypeId=" + urlTypeModel.TypeId;
                    try
                    {
                        if (DBHelper.ExecuteNonQuery(strSql) > 0)
                        {
                            result.Code = 1;
                        }
                    }
                    catch (Exception x)
                    {
                        result.Code = 0;
                        result.Msg = x.Message;
                    }
                }
            }

            return Json(result);
        }

        /// <summary>
        /// Deletes the URL type by identifier.  数据删除
        /// </summary>
        /// <param name="TypeId">The type identifier.</param>
        /// <returns></returns>
        /// 创建者：刘靖文
        /// 创建时间：2019/5/10 15:06
        public ActionResult DelUrlTypeById(int TypeId)
        {
            ResultModel result = new ResultModel();
            try
            {
                string strSqlByTypeId = "SELECT COUNT(1) FROM dbo.T_Url WHERE UrlType=" + TypeId;
                if (Convert.ToInt32(DBHelper.ExecuteScalar(strSqlByTypeId)) > 0)
                {
                    //提示已存在
                    result.Code = 2;
                }
                else
                {
                    string strSql = "DELETE FROM dbo.T_UrlType WHERE TypeId=" + TypeId;
                    if (DBHelper.ExecuteNonQuery(strSql) > 0)
                    {
                        result.Code = 1;
                    }
                }

            }
            catch (Exception x)
            {
                result.Code = 0;
                result.Msg = x.Message;
            }
            return Json(result);
        }

        #endregion

    }
}